<?php
namespace app\manage\controller;
use think\Db;

class AuditCpMng extends Base
{
    //CP对账列表
    public function index(){
        if(request()->isAjax()){
            $page = input('page');
            $limit = input('limit');
            $id = input('id');
            $gameId = input('game_id');
            $date = input('transfer_time');
            $balanceType = input('balance_type');
            if( !empty($date) ){
                $arr = explode(' - ', $date);
                $transferTime['start_time'] = strtotime($arr[0]);
                $transferTime['end_time'] = strtotime($arr[1])+86399;
            }else{
                $transferTime = false;
            }

            $where = '1';
            !empty($id) && $where .= " && id={$id}";
            !empty($gameId) && $where .= " && game_id={$gameId}";
            !empty($transferTime) && $where .= " && transfer_time between {$transferTime['start_time']} AND {$transferTime['end_time']}";
            !empty($balanceType) && $where .= " && balance_type={$balanceType}";
            $where .= model('Game')->gameClient(input('game_server'), input('game_publish'), input('game_platform'), input('game_client'));
            $where=='1' && $where = [];
            if( ! $count=model('CpBill')->where($where)->count() ){
               $this->layTable();
            }

            $select = model('CpBill')
                ->where($where)
                ->limit(($page-1)*$limit, $limit)
                ->order('id desc')
                ->select();
            $collect = collection($select)->append(['game_name', 'daytime']);

            //获取渠道率
            $channelSelect = model('Channel')->field('channel_id,pay_rate,tax_rate,channel_rate')->where('channel_id', '>=', 5000)->select();
            $channelRate = [];
            foreach ($channelSelect as $k=>$v){
                $channelRate[$v['channel_id']]['pay_rate'] = $v['pay_rate'];
                $channelRate[$v['channel_id']]['channel_rate'] = $v['channel_rate'];
                $channelRate[$v['channel_id']]['tax_rate'] = $v['tax_rate'];
            }

            //测试
//            $minTme = model('CpBill')->where($where)->limit(($page-1)*$limit, $limit)->max('start_time');
//            $maxTime = model('CpBill')->where($where)->limit(($page-1)*$limit, $limit)->max('end_time');
//            $gameArr = model('CpBill')->where($where)->limit(($page-1)*$limit, $limit)->column('game_id');
//            $gameStr = implode(',', $gameArr);
//            $whereOrder = "pay_time between '{$minTme}' AND '{$maxTime}' && debug='0' && pay_result='1' && game_id in ({$gameStr}) && channel_id>=5000"; //该游戏的所有联运充值
//
//            $selectOrder = Db::name('PayOrderY_'.date('Y', $minTme))->field('channel_id, game sum(total_price) as total_price')->where($whereOrder)->group('channel_id')->select();
//            $aa = collection($selectOrder)->toArray();
//            p($aa);
//            p($minTme);
            $data = [];
            foreach ($collect as $k=>$v){
                $startTime = strtotime(date('Y-m', strtotime($v['start_time'])));
                $endTime = strtotime($v['end_time'])+86399;
                //计算需回款总额
                $whereOrder = "pay_time between '{$startTime}' AND '{$endTime}' && debug='0' && pay_result='1' && game_id='{$v['game_id']}' && channel_id>=5000"; //该游戏的所有联运充值
                $selectOrder = Db::name('PayOrderY_'.date('Y', $startTime))->field('channel_id, sum(total_price) as total_price')->where($whereOrder)->group('channel_id')->select();

                $sdkFinishPrice = 0;
                $sdkBackPrice = 0;
                $sdkNoPrice = 0;
                foreach ($selectOrder as $aa=>$bb){
                    //统计每个渠道的需回款金额
                    $receiptPrice = ($bb['total_price']/100)*($channelRate[$bb['channel_id']]['pay_rate']/100)*(1-$channelRate[$bb['channel_id']]['tax_rate']/100)*(1-$channelRate[$bb['channel_id']]['channel_rate']/100);

                    //统计每个渠道的该回款金额
                    $whereBill['start_time'] = $startTime;
                    $whereBill['end_time'] = $endTime;
                    $whereBill['channel_id'] = $bb['channel_id'];
                    $whereBill['game_id'] = $v['game_id'];
                    $whereBill['status'] = 1;

                    if( model('SdkBillDetail')->where($whereBill)->count() ){
                        $sdkBackPrice += $receiptPrice;
                    }

                    $sdkFinishPrice += $receiptPrice;
                }
                $sdkFinishPrice = round($sdkFinishPrice, 2);
                $sdkBackPrice = round($sdkBackPrice, 2);
                $sdkNoPrice = $sdkFinishPrice-$sdkBackPrice;

                if($sdkFinishPrice!=0 && $sdkBackPrice!=0){
                    $sdkFinishRate = round(($sdkBackPrice/$sdkFinishPrice)*100, 2) . '%';
                }else{
                    $sdkFinishRate = '0%';
                }

                $data[] = [
                    'id' => $v['id'],
                    'game_id' => $v['game_id'] . '：' . $v['game_name'],
                    'daytime' => $v['daytime'],
                    'type1' => 'CPS',
                    'type2' => '联运',
                    'cps_pay_price' => $v['cps_pay_price'],
                    'sdk_pay_price' => $v['sdk_pay_price'],
                    'channel_rate' => $v['channel_rate'],
                    'pay_rate' => $v['pay_rate'],
                    'tax_rate' => $v['tax_rate'],
                    'cps_total_price' => $v['cps_total_price'],
                    'sdk_total_price' => $v['sdk_total_price'],
                    'balance_time' => $v['balance_time'],
                    'transfer_time' => $v['transfer_time'],
                    'invoice_type' => $v['invoice_type'],
                    'status' => $v['status'],
                    'cps_fixed_price' => $v['cps_fixed_price'],
                    'sdk_fixed_price' => $v['sdk_fixed_price'],
                    'cps_diff_price' => $v['cps_fixed_price']-$v['cps_pay_price'],
                    'sdk_diff_price' => $v['sdk_fixed_price']-$v['sdk_pay_price'],
                    'all_total_price' => $v['all_total_price'],
                    'remark' => $v['remark'],
                    'urge' => $v['urge'],
                    'get' => $v['get'],
                    'balance_type' => $v['balance_type']==1 ? '公账' : '私账',
                    'server_price' => $v['server_price'],
                    'sdk_finish_price' => $sdkFinishPrice, //需回款金额
                    'sdk_back_price' => $sdkBackPrice, //已回款金额
                    'sdk_finish_rate' => $sdkFinishRate, //回款率
                    'sdk_no_price' => $sdkNoPrice, //未回款
                    'fact_price' => $v['fact_price'], //实际充值
                ];
            }

            $this->layTable(0, 'CP对账列表', $data, $count);
        }else{
            return view();
        }
    }

    //查询金额
    public function select(){
        $gameId = input('game_id');
        $date = explodeTime(input('date'));
        $invoiceType = input('invoice_type');
        $balanceType = input('balance_type');

        $whereEdit['game_id'] = $gameId;
        $whereEdit['balance_type'] = $balanceType;
        if($daytime = model('CpBill')->field('start_time, end_time')->where($whereEdit)->select()){
            foreach ($daytime as $k=>$v){
                if(($date['start_time']>=strtotime($v['start_time']) && $date['start_time']<=strtotime($v['end_time'])+86399) || ($date['end_time']>=strtotime($v['start_time']) && $date['end_time']<=strtotime($v['end_time'])+86399)){
                    $this->layJson(1, "{$gameId} 游戏号存在日期重叠的账单");
                }
            }
        }

        $where = "pay_time between '{$date['start_time']}' AND '{$date['end_time']}' && debug='0' && pay_result='1' && game_id='{$gameId}'";

        //跨年分表
        $y1 = date('Y', $date['start_time']);
        $y2 = date('Y', $date['end_time']);
        if($y1 == $y2){
            $where1 = $where . " && channel_id='1000'";
            $cps_pay_price = Db::name('PayOrderY_'.$y1)->where($where1)->sum('total_price');

            $where2 = $where . " && channel_id>='5000'";
            $sdk_pay_price = Db::name('PayOrderY_'.$y1)->where($where2)->sum('total_price');
        }else{
            $field = "total_price";

            $where1 = $where . " && channel_id='1000'";
            $subQuery = Db::name('PayOrderY_'.$y1)
                ->field($field)
                ->where($where1)
                ->union("select {$field} from sdk_pay_order_y_{$y2} where ({$where1})", true)
                ->buildSql();
            $cps_pay_price = Db::table($subQuery.' y')->sum('total_price');

            $where2 = $where . " && channel_id>='5000'";
            $subQuery = Db::name('PayOrderY_'.$y1)
                ->field($field)
                ->where($where2)
                ->union("select {$field} from sdk_pay_order_y_{$y2} where ({$where2})", true)
                ->buildSql();
            $sdk_pay_price = Db::table($subQuery.' y')->sum('total_price');
        }

        //CPS充值
        $data['cps_pay_price'] = $data['cps_fixed_price'] = round($cps_pay_price/100, 2);

        //联运充值
        $data['sdk_pay_price'] = $data['sdk_fixed_price'] = round($sdk_pay_price/100, 2);

        //渠道费率
        $game = model('ViewGameManage')->field('channel_rate, pay_rate, pay_rate_private')->where('game_id', $gameId)->find();

        if($balanceType == 1){ //对公
            //24%|≤|30w|≥|15%
            if(strpos($game['pay_rate'], 'w')){
                $data['pay_rate'] = $this->checkPayRate($game['pay_rate'], $data['cps_pay_price']+$data['sdk_pay_price']);
            }else{
                $data['pay_rate'] = $game['pay_rate'];
            }

            $cpInvoiceType = config('cp_invoice_type');
            foreach ($cpInvoiceType as $k=>$v){
                if($v['type'] == $invoiceType){
                    $data['tax_rate'] = $v['rate'];
                }
            }
            $data['channel_rate'] = $game['channel_rate'];
        }else if($balanceType == 2){ //对私
            if(strpos($game['pay_rate'], 'w')){
                $data['pay_rate'] = $this->checkPayRate($game['pay_rate_private'], $data['cps_pay_price']+$data['sdk_pay_price']);
            }else{
                $data['pay_rate'] = $game['pay_rate_private'];
            }

            $data['tax_rate'] = 8;
            $data['channel_rate'] = 0;
        }else { //服务器费用
            $data['pay_rate'] = 0;
            $data['tax_rate'] = 0;
            $data['channel_rate'] = 0;
        }

        //CPS结算
        $data['cps_total_price'] = round($data['cps_pay_price']*($data['pay_rate']/100)*(1-($data['channel_rate']/100))*(1-($data['tax_rate']/100)), 2);

        //联运结算
        $data['sdk_total_price'] = round($data['sdk_pay_price']*($data['pay_rate']/100)*(1-($data['channel_rate']/100))*(1-($data['tax_rate']/100)), 2);

        //合计
        $data['all_total_price'] = round($data['cps_total_price']+$data['sdk_total_price'], 2);

        $this->layJson(0, '', $data);
    }

    //添加
    public function add(){
        if( input('game_id') ){
            $data = input();
            $date= explodeDate($data['date']);
            $payType = $data['pay_type'];
            unset($data['date']);
            unset($data['pay_type']);
            $date['result']==false ? $this->error($date['msg']) : $date = $date['data'];

            $data['status'] = 1;
            $data['start_time'] = $date['start_time'];
            $data['end_time'] = $date['end_time'];
            $data['fact_price'] = $data['all_total_price'];
            if(model('CpBill')->where($data)->count()){
                $this->success('提交成功');
            }

            if( !model('CpBill')->save($data) ){
               $this->layJson(1, '生成结算单失败，请重试');
            }

            if($payType==2) $this->success('提交成功');

            $find = model('CpBill')->where('id', model('CpBill')->id)->find();
            $info = $find->append(['game_name'])->toArray();

            //生成结算单 米娱bt-cp-流水结算单
            $excelName = $info['game_name'].date('m', strtotime($info['start_time'])).'月';
            $this->exportExcel($info, $excelName, $excelName);
        }else{
            return view();
        }
    }

    //编辑
    public function edit(){
        if( input('game_id') ){
            $data = input();
            $date= explodeDate($data['date']);
            $payType = $data['pay_type'];
            unset($data['date']);
            unset($data['pay_type']);

            $date['result']==false ? $this->error($date['msg']) : $date = $date['data'];

            $data['start_time'] = $date['start_time'];
            $data['end_time'] = $date['end_time'];

            if( model('CpBill')->isUpdate(true)->save($data)===false ){
                $this->layJson(1, '生成结算单失败，请重试');
            }

            if($payType==2) $this->success('提交成功');

            $find = model('CpBill')->where('id', model('CpBill')->id)->find();
            $info = $find->append(['game_name'])->toArray();

            //生成结算单
            $excelName = $info['game_name'].date('m', strtotime($info['start_time'])).'月';
            $this->exportExcel($info, $excelName, $excelName);
        }else{
            $find = model('CpBill')->find( input('id') );

            return view('', ['find'=>$find]);
        }
    }

    //删除
    public function del(){
        model('CpBill')->destroy(input('id')) ? $this->layJson(0, '删除成功') : $this->layJson(1, '删除失败，请重试');
    }

    //查看联运
    public function view(){
        if(request()->isAjax()){
//            $page = input('page');
//            $limit = input('limit');
            $id = input('id');

            $v = model('CpBill')->where('id', $id)->find();

            $startTime = strtotime(date('Y-m', strtotime($v['start_time'])));
            $endTime = strtotime($v['end_time'])+86399;
            //计算需回款总额
            $whereOrder = "pay_time between '{$startTime}' AND '{$endTime}' && debug='0' && pay_result='1' && game_id='{$v['game_id']}' && channel_id>=5000"; //该游戏的所有联运充值

            $count = Db::name('PayOrderY_'.date('Y', $startTime))->where($whereOrder)->group('channel_id')->count();
            $selectOrder = Db::name('PayOrderY_'.date('Y', $startTime))->field('channel_id, sum(total_price) as total_price')->where($whereOrder)->group('channel_id')->select();

            $data = [];
            $channelIdName = model('Channel')->channelIdName();
            $gameIdName = model('Game')->gameIdName();

            $sdkFinishPrice = 0;
            foreach ($selectOrder as $aa=>$bb){
                $channelBill = model('Channel')->field('pay_rate,tax_rate,channel_rate')->where('channel_id', $bb['channel_id'])->find();
                $sdkFinishPrice += ($bb['total_price']/100)*($channelBill['pay_rate']/100)*(1-$channelBill['tax_rate']/100)*(1-$channelBill['channel_rate']/100);
            };

            foreach ($selectOrder as $kk=>$vv){
                $whereBill['start_time'] = $startTime;
                $whereBill['end_time'] = $endTime;
                $whereBill['game_id'] = $v['game_id'];
                $whereBill['channel_id'] = $vv['channel_id'];
                $whereBill['status'] = 1;
                $isReceipt = model('SdkBillDetail')->where($whereBill)->count();

                $channelBill = model('Channel')->field('pay_rate,tax_rate,channel_rate')->where('channel_id', $vv['channel_id'])->find();

                $receiptPrice = ($vv['total_price']/100)*($channelBill['pay_rate']/100)*(1-$channelBill['tax_rate']/100)*(1-$channelBill['channel_rate']/100);

                $data[] = [
                    'date' => $v['start_time'] . ' - ' .$v['end_time'],
                    'game_id' => $v['game_id'] . '：' . $gameIdName[ $v['game_id'] ],
                    'channel_id' => $vv['channel_id'] . '：' . $channelIdName[ $vv['channel_id'] ],
                    'receipt_price' => round($receiptPrice, 2),
                    'is_receipt' => $isReceipt ? '已回款' : '',
                    'receipt_rate' => round(($receiptPrice/$sdkFinishPrice)*100, 2) . '%',
                ];
            }

            $this->layTable(0, 'sdk回款记录', $data, $count);
        }else{
            return view();
        }
    }

    //已核对
    public function receive(){
        $where['id'] = ['in', input('id')];
        $where['status'] = 1;
        $idArr = model('CpBill')->where($where)->column('id');
        $update = [];
        foreach ($idArr as $k=>$v){
            $update[] = [
                'id' => $v,
                'status' => 2
            ];
        }
        model('CpBill')->saveAll($update) ? $this->layJson(0, '修改成功') : $this->layJson(1, '修改失败，请重试');
    }

    //已转账
    public function finish(){
        $where['id'] = ['in', input('id')];
        $where['status'] = 2;
        if( $bill = model('CpBill')->field('id,game_id,end_time')->where($where)->select() ){
            Db::startTrans();
            try{
                $update = [];
                foreach ($bill as $k=>$v){
                    $update[] = [
                        'id' => $v['id'],
                        'status' => 3,
                        'transfer_time' => time(),
                    ];
                    model('GameExtend')->save(['audit_time'=>strtotime($v['end_time'])+86399], ['game_id'=>$v['game_id']]);
                }
                model('CpBill')->saveAll($update);
                Db::commit();
                $this->layJson(0, '修改成功');
            } catch (\Exception $e){
                Db::rollback();
                $this->layJson(1, $e->getMessage());
            }
        }else{
            $this->layJson(1, '请勾选状态为"已核对"的选择框');
        }
    }

    //标注已收件
    public function get(){
        $where['id'] = ['in', input('id')];
        $where['status'] = 1;
        if( $bill = model('CpBill')->field('id,game_id,end_time')->where($where)->select() ){
            $update = [];
            foreach ($bill as $k=>$v){
                $update[] = [
                    'id' => $v['id'],
                    'get' => 1
                ];
            }
            model('CpBill')->saveAll($update);
            $this->layJson(0, '已收件成功');
        }else{
            $this->layJson(1, '请勾选状态为"已提交"的选择框');
        }
    }

    //催单
    public function urge(){
        $where['id'] = ['in', input('id')];
        $where['status'] = 2;
        if( $bill = model('CpBill')->field('id,game_id,end_time')->where($where)->select() ){
            $update = [];
            foreach ($bill as $k=>$v){
                $update[] = [
                    'id' => $v['id'],
                    'urge' => 1
                ];
            }
            model('CpBill')->saveAll($update);
            $this->layJson(0, '催单成功');
        }else{
            $this->layJson(1, '请勾选状态为"已核对"的选择框');
        }
    }

    //获取游戏对账时间
    public function time(){
        $where['game_id'] = input('game_id');
        $where['balance_type'] = input('balance_type');
        $endTime = model('CpBill')->where($where)->order('end_time desc')->value('end_time');
        return $endTime ? $this->layJson(0, '', date('Y-m-d', $endTime+1)) : $this->layJson(0, '', '2016-01-01');
    }

    //编辑实际金额
    public function edit2(){
        model('CpBill')->isUpdate(true)->save(input())!==false ? $this->layJson(0, '修改成功') : $this->layJson(1, '修改失败，请重试');
    }

    //导出
    public function export(){
        $gameId = input('game_id');
        $date = input('transfer_time');
        $balanceType = input('balance_type');

        if( !empty($date) ){
            $arr = explode(' - ', $date);
            $transferTime['start_time'] = strtotime($arr[0]);
            $transferTime['end_time'] = strtotime($arr[1])+86399;
        }else{
            $transferTime = false;
        }

        $where = '1';
        !empty($gameId) && $where .= " && game_id={$gameId}";
        !empty($transferTime) && $where .= " && transfer_time between {$transferTime['start_time']} AND {$transferTime['end_time']}";
        !empty($balanceType) && $where .= " && balance_type={$balanceType}";
        $where .= model('Game')->gameClient(input('game_server'), input('game_publish'), input('game_platform'), input('game_client'));
        $where=='1' && $where = [];
        $select = model('CpBill')->where($where)->order('transfer_time desc')->select();

        !$select && $this->error('数据为空，请重新筛选条件');

        $collect = collection($select)->append(['game_name'])->toArray();

        foreach ($collect as $k=>$v){
            if($v['server_price']!=0){ //服务器费用
                $data[] = [
                    $v['start_time'],
                    $v['end_time'],
                    $v['game_id'],
                    $v['game_name'],
                    '服务器费用',
                    $v['server_price'],
                    $v['pay_rate'],
                    $v['server_price']*(float)$v['pay_rate']/100,
                    $v['channel_rate'],
                    $v['tax_rate'],
                    $v['all_total_price'],
                    $v['fact_price']
                ];
            }else{
                //CPS
                $data[] = [
                    $v['start_time'],
                    $v['end_time'],
                    $v['game_id'],
                    $v['game_name'],
                    'CPS',
                    $v['cps_pay_price'],
                    $v['pay_rate'],
                    $v['cps_pay_price']*(float)$v['pay_rate']/100,
                    $v['channel_rate'],
                    $v['tax_rate'],
                    $v['cps_total_price'],
                    $v['fact_price']
                ];
                //联运
                $data[] = [
                    $v['start_time'],
                    $v['end_time'],
                    $v['game_id'],
                    $v['game_name'],
                    '联运',
                    $v['sdk_pay_price'],
                    $v['pay_rate'],
                    $v['sdk_pay_price']*(float)$v['pay_rate']/100,
                    $v['channel_rate'],
                    $v['tax_rate'],
                    $v['sdk_total_price'],
                ];
            }
        }
        array_unshift($data, ['结账起始时间', '结账终点时间', '游戏号', '游戏名称', '类型', '总金额(元)', '分成比例', '金额(元)', '通道费', '税点', '合计', '实结金额(元)']);
        $this->exportExcel2($data, $date.'转账', $date.'转账');
    }

    public function exportExcel2($data,$excelFileName,$sheetTitle){
        import("php_excel.PHPExcel");

        /* 实例化类 */
        $objPHPExcel = new \PHPExcel();

        /* 设置输出的excel文件为2007兼容格式 */
        //$objWriter=new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        /* 设置当前的sheet */
        $objPHPExcel->setActiveSheetIndex(0);

        $objActSheet = $objPHPExcel->getActiveSheet();

        /*设置宽度*/
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);

        /* sheet标题 */
        $objActSheet->setTitle($sheetTitle);

        $i = 1;
        foreach($data as $value){
            $j = 'A';
            foreach($value as $value2){
                $objPHPExcel->getActiveSheet()->getStyle($j.$i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                if($i == 1){
                    $objActSheet->getStyle($j.$i,$value2)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
                    $objActSheet->getStyle($j.$i,$value2)->getFont()->setBold(true);
                    $objActSheet->getStyle($j.$i,$value2)->getFont()->setSize(11);
                    $objActSheet->getStyle($j.$i,$value2)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                    $objActSheet->getStyle($j.$i,$value2)->getFill()->getStartColor()->setRGB("4575d6");
                }
                if($j=='E' && $value2=='联运'){
                    $a = $i-1;
                    $objPHPExcel->getActiveSheet()->mergeCells("L{$a}:L{$i}");
                    $objPHPExcel->getActiveSheet()->getStyle("L{$a}")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                }
                $objActSheet->setCellValue($j.$i,$value2);
                $j++;
            }
            $i++;
        }

        /* 生成到浏览器，提供下载 */
        ob_end_clean();  //清空缓存
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="'.$excelFileName.'.xlsx"');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

    //导出Excel表格
    private function exportExcel($data,$excelFileName,$sheetTitle){
        import("php_excel.PHPExcel");

        /* 实例化类 */
        $objPHPExcel = new \PHPExcel();

        /* 设置输出的excel文件为2007兼容格式 */
        //$objWriter=new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        /* 设置当前的sheet */
        $objPHPExcel->setActiveSheetIndex(0);

        $objActSheet = $objPHPExcel->getActiveSheet();

        /* sheet标题 */
        $objActSheet->setTitle($sheetTitle);

        $objPHPExcel->getActiveSheet()->mergeCells('A8:G8');
        $objPHPExcel->getActiveSheet()->mergeCells('A16:D16');
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(95);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', '米娱-手机《'.$data['game_name'].'》结算表'.PHP_EOL.'（'.$data['start_time'].' - '.$data['end_time'].'）'); //设置文字
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //水平垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('A13')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('E13')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->mergeCells('A2:B2'); //合并单元格
        $objPHPExcel->getActiveSheet()->setCellValue('A2', '结算月份');
        $objPHPExcel->getActiveSheet()->setCellValue('C2', '充值金额');
        $objPHPExcel->getActiveSheet()->setCellValue('D2', '渠道费率');
        $objPHPExcel->getActiveSheet()->setCellValue('E2', '税率');
        $objPHPExcel->getActiveSheet()->setCellValue('F2', '结算比例');
        $objPHPExcel->getActiveSheet()->setCellValue('G2', '结算金额(元)');
        $objPHPExcel->getActiveSheet()->mergeCells('A3:A4'); //合并单元格
        $objPHPExcel->getActiveSheet()->setCellValue('A3', date('Y年m月', strtotime($data['start_time'])));
        $objPHPExcel->getActiveSheet()->setCellValue('B3', 'CPS数据');
        $objPHPExcel->getActiveSheet()->setCellValue('B4', '联运数据');
        $objPHPExcel->getActiveSheet()->setCellValue('C3', $data['cps_pay_price']);
        $objPHPExcel->getActiveSheet()->setCellValue('C4', $data['sdk_pay_price']);
        $objPHPExcel->getActiveSheet()->setCellValue('D3', $data['channel_rate']);
        $objPHPExcel->getActiveSheet()->setCellValue('D4', $data['channel_rate']);
        $objPHPExcel->getActiveSheet()->setCellValue('E3', $data['tax_rate']);
        $objPHPExcel->getActiveSheet()->setCellValue('E4', $data['tax_rate']);
        $objPHPExcel->getActiveSheet()->setCellValue('F3', $data['pay_rate']);
        $objPHPExcel->getActiveSheet()->setCellValue('F4', $data['pay_rate']);
        $objPHPExcel->getActiveSheet()->setCellValue('G3', '=round(C3*F3*(1-D3)*(1-E3),2)');
        $objPHPExcel->getActiveSheet()->setCellValue('G4', '=round(C4*F4*(1-D4)*(1-E4),2)');
        $objPHPExcel->getActiveSheet()->mergeCells('A5:F5'); //合并单元格
        $objPHPExcel->getActiveSheet()->setCellValue('A5', '合计');
        $objPHPExcel->getActiveSheet()->setCellValue('G5', '=round(SUM(G3:G4),2)');
        $objPHPExcel->getActiveSheet()->mergeCells('A6:B6');
        $objPHPExcel->getActiveSheet()->setCellValue('A6', '支付金额(大写): ');
        $objPHPExcel->getActiveSheet()->mergeCells('C6:G6');
        $objPHPExcel->getActiveSheet()->setCellValue('C6', '=IF(ROUND(G5,2)<0,"无效数值",IF(ROUND(G5,2)=0,"零",IF(ROUND(G5,2)<1,"",TEXT(INT(ROUND(G5,2)),"[dbnum2]")&"元")&IF(INT(ROUND(G5,2)*10)-INT(ROUND(G5,2))*10=0,IF(INT(ROUND(G5,2))*(INT(ROUND(G5,2)*100)-INT(ROUND(G5,2)*10)*10)=0,"","零"),TEXT(INT(ROUND(G5,2)*10)-INT(ROUND(G5,2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(G5,2)*100)-INT(ROUND(G5,2)*10)*10)=0,"整",TEXT((INT(ROUND(G5,2)*100)-INT(ROUND(G5,2)*10)*10),"[dbnum2]")&"分")))');
        $objPHPExcel->getActiveSheet()->mergeCells('A7:B7');
        $objPHPExcel->getActiveSheet()->setCellValue('A7', '备注:');
        $objPHPExcel->getActiveSheet()->mergeCells('C7:G7');
        $objPHPExcel->getActiveSheet()->setCellValue('C7', '结算金额=充值金额*结算比例%*（1-渠道费率）*（1-税率）');

        $objPHPExcel->getActiveSheet()->mergeCells('A9:D9');
        $objPHPExcel->getActiveSheet()->setCellValue('A9', '甲方：上海米娱网络科技有限公司');
        $objPHPExcel->getActiveSheet()->mergeCells('A10:D10');
        $objPHPExcel->getActiveSheet()->setCellValue('A10', '盖章：');
        $objPHPExcel->getActiveSheet()->mergeCells('A11:D11');
        $objPHPExcel->getActiveSheet()->setCellValue('A11', '发票类型：'.$data['invoice_type']);
        $objPHPExcel->getActiveSheet()->mergeCells('A12:D12');
        $objPHPExcel->getActiveSheet()->setCellValue('A12', '开票项目:信息服务费');
        $objPHPExcel->getActiveSheet()->mergeCells('A13:D13');
        $objPHPExcel->getActiveSheet()->setCellValue('A13', '地址：广州市中山大道西307号东岳财富广场C座603房');
        $objPHPExcel->getActiveSheet()->mergeCells('A14:D14');
        $objPHPExcel->getActiveSheet()->setCellValue('A14', '联系人：陈子华');
        $objPHPExcel->getActiveSheet()->mergeCells('A15:D15');
        $objPHPExcel->getActiveSheet()->setCellValue('A15', '联系电话：18316612244');
        $objPHPExcel->getActiveSheet()->mergeCells('A17:D17');
        $objPHPExcel->getActiveSheet()->setCellValue('A17', '结算日期：'.date('Y年m月d日', strtotime($data['balance_time'])));

        $objPHPExcel->getActiveSheet()->mergeCells('E9:G9');
        $objPHPExcel->getActiveSheet()->setCellValue('E9', '乙方：');
        $objPHPExcel->getActiveSheet()->mergeCells('E10:G10');
        $objPHPExcel->getActiveSheet()->setCellValue('E10', '盖章：');
        $objPHPExcel->getActiveSheet()->mergeCells('E11:G11');
        $objPHPExcel->getActiveSheet()->setCellValue('E11', '联系人：');
        $objPHPExcel->getActiveSheet()->mergeCells('E12:G12');
        $objPHPExcel->getActiveSheet()->setCellValue('E12', '联系电话：');
        $objPHPExcel->getActiveSheet()->mergeCells('E13:G13');
        $objPHPExcel->getActiveSheet()->setCellValue('E13', '地址：');
        $objPHPExcel->getActiveSheet()->mergeCells('E14:G14');
        $objPHPExcel->getActiveSheet()->setCellValue('E14', '请汇入此账号：');
        $objPHPExcel->getActiveSheet()->mergeCells('E15:G15');
        $objPHPExcel->getActiveSheet()->setCellValue('E15', '户  名：');
        $objPHPExcel->getActiveSheet()->mergeCells('E16:G16');
        $objPHPExcel->getActiveSheet()->setCellValue('E16', '帐  号：');
        $objPHPExcel->getActiveSheet()->mergeCells('E17:G17');
        $objPHPExcel->getActiveSheet()->setCellValue('E17', '开户行：');

        $objPHPExcel->getActiveSheet()->mergeCells('A18:G18');
        $objPHPExcel->getActiveSheet()->setCellValue('A18', '--本行以下无正文--');

        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('5')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('6')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('7')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('8')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('9')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('10')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('11')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('12')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('13')->setRowHeight(34);
        $objPHPExcel->getActiveSheet()->getRowDimension('14')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('15')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('16')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('17')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('18')->setRowHeight(28);

        $objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(12);

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('G3')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('G4')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('G5')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('E4')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('F3')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('F4')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('A13')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setSize(12);


        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B4')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C4')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D4')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E4')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F4')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G4')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A6')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G5')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('G5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E13')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet->getColumnDimension('A')->setWidth(12);
        $objActSheet->getColumnDimension('B')->setWidth(11);
        $objActSheet->getColumnDimension('C')->setWidth(11);
        $objActSheet->getColumnDimension('D')->setWidth(11);
        $objActSheet->getColumnDimension('E')->setWidth(11);
        $objActSheet->getColumnDimension('F')->setWidth(11);
        $objActSheet->getColumnDimension('G')->setWidth(14);

        $objPHPExcel->getActiveSheet()->getStyle('A1:G18')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

        ob_end_clean();  //清空缓存
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="'.$excelFileName.'.xlsx"');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

    //检查阶梯充值比例格式
    private function checkPayRate($payRate, $balancePrice){
        $payStr = str_replace(['%','w'],'',$payRate);
        $payArr = explode('|', $payStr);

        if($payArr[1] == '≤'){
            if($balancePrice <= $payArr[2]*10000){
                return $payArr[0];
            }
        }else if($payArr[1] == '＜'){
            if($balancePrice < $payArr[2]*10000){
                return $payArr[0];
            }
        }

        if($payArr[3] == '≥'){
            if($balancePrice >= $payArr[2]*10000){
                return $payArr[4];
            }
        }else if($payArr[3] == '＞'){
            if($balancePrice > $payArr[2]*10000){
                return $payArr[4];
            }
        }
    }
}